﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace DAL
{
   public class ADDDAL
    {
       SqlConnection str = new SqlConnection("Data Source=PC201609040948;Initial Catalog=TianTianDai_Db;Persist Security Info=True;User ID=sa;pwd=123456");
        /// <summary>
        /// 加入
        /// </summary>
        /// <returns></returns>
        public List<Project> ShowADD()
        {
            str.Open();
            string sql = "select * from Project";
            SqlDataAdapter adap = new SqlDataAdapter(sql, str);
            DataTable ta = new DataTable();
            adap.Fill(ta);
            str.Close();
            List<Project> listProject = new List<Project>();
            foreach (DataRow item in ta.Rows)
            {
                Project j = new Project();
                j.ProjectId = Convert.ToInt32(item["ProjectId"]);
                j.ProjectName = item["ProjectName"].ToString();
                j.ProjectNum = Convert.ToInt32(item["ProjectNum"]);
                listProject.Add(j);
            }
            return listProject;

        }
       /// <summary>
       /// 删除信息
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
        public int DeleteADD(int id)
        {
            str.Open();
            string sql = "delete Project where ProjectId='"+id+"'";
            SqlCommand com = new SqlCommand(sql,str);
            var i= com.ExecuteNonQuery();
            return i;
        }
       /// <summary>
       /// 添加信息
       /// </summary>
       /// <param name="p"></param>
       /// <returns></returns>
        public int Add_Add(Model.Project p)
        {
            str.Open();
            string sql = "insert into Project values('" +p.ProjectName+ "','"+p.ProjectNum+"')";
            SqlCommand com = new SqlCommand(sql,str);
            var i = com.ExecuteNonQuery();
            return i;
        }
       /// <summary>
       /// 显示详细信息
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
        public Project ShowProjectX(int id)
        {
            str.Open();
            string sql = "select * from Project where ProjectId='"+id+"'";
            SqlDataAdapter adap = new SqlDataAdapter(sql, str);
            DataTable ta = new DataTable();
            adap.Fill(ta);
            str.Close();
            List<Project> listProject = new List<Project>();
            foreach (DataRow item in ta.Rows)
            {
                Project j = new Project();
                j.ProjectId = Convert.ToInt32(item["ProjectId"]);
                j.ProjectName = item["ProjectName"].ToString();
                j.ProjectNum = Convert.ToInt32(item["ProjectNum"]);
                listProject.Add(j);
            }
            return listProject.FirstOrDefault();
        }
       /// <summary>
       /// 修改信息
       /// </summary>
       /// <param name="data"></param>
       /// <returns></returns>
        public int UpdateADD(Project data)
        {
            str.Open();
            string sql = "update Project set  ProjectName='"+data.ProjectName+"',ProjectNum='"+data.ProjectNum+"' where ProjectId='"+data.ProjectId+"'";
            SqlCommand com = new SqlCommand(sql,str);
            int i = com.ExecuteNonQuery();
            return i;
        }
       /// <summary>
       /// 搜索
       /// </summary>
       /// <param name="pp"></param>
       /// <returns></returns>
        public List<Project> SeleteProject(string name)
        {
            str.Open();
            string sql = "selete ProjectId,ProjectName,ProjectNum from Project where ProjectName like '"+name+"'";
            SqlDataAdapter adap = new SqlDataAdapter(sql,str);
            DataTable ta = new DataTable();
            adap.Fill(ta);
            List<Project> listProject = new List<Project>();
            foreach (DataRow item in ta.Rows)
            {
                Project j = new Project();
                j.ProjectId = Convert.ToInt32(item["ProjectId"]);
                j.ProjectName = item["ProjectName"].ToString();
                j.ProjectNum = Convert.ToInt32(item["ProjectNum"]);
                listProject.Add(j);
            }
            return listProject;
        }
    }
}
